<?php
class Rate extends CActiveRecord
{
    public function buildRate($month, $year, $next_month, $next_year)
    {                
        /* Kiểm tra xem tháng tiếp theo đã có rate chưa */
        $next_condition = " month=" . $next_month . " AND year=" . $next_year;
        $sql = "SELECT count(id) FROM c_rate WHERE " . $next_condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
    
        /* Nếu chưa có thì insert */
        if($rows["count(id)"] == 0){            
            $condition = " month=" . $month . " AND year=" . $year;
            $sql = "SELECT rate_of_user, rate_of_mtop, type FROM c_rate WHERE " . $condition;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();    

            $sql = "INSERT INTO c_rate SET 
                month=:month
                , year=:year
                , rate_of_user=:rate_of_user
                , rate_of_mtop=:rate_of_mtop
                , time=:time
                , type=:type
            ";
            $command = Yii::app()->db->createCommand($sql);
            foreach($rows as $row){
                $row["month"] = $next_month;
                $row["year"] = $next_year;
                $row["time"] = $next_year . "-" . $next_month . "-01";
                
                $command->bindValues($row);                
                $result = $command->execute();
            }
        } else {
            $result = -1;
        }

        return $result;
    }

    public function buildRateSms($month, $year, $next_month, $next_year)
    {
        /* Kiểm tra xem tháng tiếp theo đã có rate chưa */
        $next_condition = " month=" . $next_month . " AND year=" . $next_year;
        $sql = "SELECT count(id) FROM c_rate_sms WHERE " . $next_condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();

        /* Nếu chưa có thì insert */
        if($rows["count(id)"] == 0){
            $condition = " month=" . $month . " AND year=" . $year;
            $sql = "SELECT
                rate
                , serviceCode                
            FROM c_rate_sms WHERE " . $condition;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();    

            $sql = "INSERT INTO c_rate_sms SET 
                month=:month
                , year=:year
                , serviceCode=:serviceCode
                , rate=:rate
                , time=:time                
            ";
            $command = Yii::app()->db->createCommand($sql);
            foreach($rows as $row){
                $row["month"] = $next_month;
                $row["year"] = $next_year;
                $row["time"] = $next_year . "-" . $next_month . "-01";;
                $command->bindValues($row);                
                $result = $command->execute();
            }
        } else {
            $result = -1;
        }

        return $result;   
    }  

    public function getDataByMonthAndYear($month,$year){
        $sql = "SELECT * FROM c_rate WHERE month = ".intval($month)." AND year = ".intval($year)."";
        $command = Yii::app()->db->createCommand($sql);
        $data = $command->queryRow();
        return $data;
    }
    public function getDataByMonthAndYearAndType($month,$year,$type){
        $sql = "SELECT * FROM c_rate WHERE month = ".intval($month)." AND year = ".intval($year)." AND type=".intval($type)."";
        $command = Yii::app()->db->createCommand($sql);
        $data = $command->queryRow();
        return $data;
    }
    public function getDataSMSByMonthAndYear($month,$year){
        $sql = "SELECT * FROM c_rate_sms WHERE month = ".intval($month)." AND year = ".intval($year).""; 
        $command = Yii::app()->db->createCommand($sql);
        $data = $command->queryAll();
        return $data;
    }
    public function getAllRateSms()
    {
        $sql = "SELECT * FROM c_rate_sms";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        $rate = array();
        foreach($rows as $row){
            $rate[$row["month"] . "_" . $row["year"]][$row["serviceNumber"]] = $row["rate"];            
        }
        return $rate;    
    }
}
?>